﻿using System;
using System.Collections.Generic;
using System.Text;
using OgilvyOne.Borad.Model;
using OgilvyOne.Borad.IDAL;

using System.Data.SqlClient;
using System.Data;

namespace OgilvyOne.Borad.DAL
{
    public class QuestionsSUVDAL:IDALQuestionsSUV
    {
        private string connstr = SQLHelper.CONN_STRING_NON_DTC;

        /// <summary>
        /// 实现添加方法
        /// </summary>
        /// <param name="news"></param>
        /// <returns></returns>
        public int Add(QuestionsModel model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter{ ParameterName="@Q1", SqlDbType=SqlDbType.NVarChar, Value=model.Q1},
                new SqlParameter{ParameterName="@Q1text",SqlDbType=SqlDbType.NVarChar,Value=model.Q1text},
                new SqlParameter{ParameterName="@Q2",SqlDbType=SqlDbType.NVarChar,Value=model.Q2},
                new SqlParameter{ParameterName="@Q3",SqlDbType=SqlDbType.NVarChar,Value=model.Q3},
                new SqlParameter{ParameterName="@Q4", SqlDbType=SqlDbType.NVarChar, Value=model.Q4},
                new SqlParameter{ParameterName="@Q4text", SqlDbType=SqlDbType.NVarChar, Value=model.Q4text},
                new SqlParameter{ParameterName="@Q5", SqlDbType=SqlDbType.NVarChar, Value=model.Q5},
                new SqlParameter{ParameterName="@Q6", SqlDbType=SqlDbType.NVarChar, Value=model.Q6},
                new SqlParameter{ParameterName="@Q7", SqlDbType=SqlDbType.NVarChar, Value=model.Q7},
                new SqlParameter{ParameterName="@Q7text", SqlDbType=SqlDbType.NVarChar, Value=model.Q7text},
                new SqlParameter{ParameterName="@Q8", SqlDbType=SqlDbType.NVarChar, Value=model.Q8},
                new SqlParameter{ParameterName="@Q9", SqlDbType=SqlDbType.NVarChar, Value=model.Q9},
                new SqlParameter{ParameterName="@Q9text", SqlDbType=SqlDbType.NVarChar, Value=model.Q9text},
                new SqlParameter{ParameterName="@Q10_1", SqlDbType=SqlDbType.NVarChar, Value=model.Q10_1},
                new SqlParameter{ParameterName="@Q10_2", SqlDbType=SqlDbType.NVarChar, Value=model.Q10_2},
                new SqlParameter{ParameterName="@Q10_3", SqlDbType=SqlDbType.NVarChar, Value=model.Q10_3},
                new SqlParameter{ParameterName="@Q10text", SqlDbType=SqlDbType.NVarChar, Value=model.Q10text},
                new SqlParameter{ParameterName="@Q11", SqlDbType=SqlDbType.NVarChar, Value=model.Q11},
                new SqlParameter{ParameterName="@Q11text", SqlDbType=SqlDbType.NVarChar, Value=model.Q11text},
                new SqlParameter{ParameterName="@Q12_1", SqlDbType=SqlDbType.NVarChar, Value=model.Q12_1},
                new SqlParameter{ParameterName="@Q12_2", SqlDbType=SqlDbType.NVarChar, Value=model.Q12_2},
                new SqlParameter{ParameterName="@Q12_3", SqlDbType=SqlDbType.NVarChar, Value=model.Q12_3},
                new SqlParameter{ParameterName="@Q12text", SqlDbType=SqlDbType.NVarChar, Value=model.Q12text},
                new SqlParameter{ParameterName="@Q13", SqlDbType=SqlDbType.NVarChar, Value=model.Q13},
                new SqlParameter{ParameterName="@Q13text", SqlDbType=SqlDbType.NVarChar, Value=model.Q13text},
                new SqlParameter{ParameterName="@Q14", SqlDbType=SqlDbType.NVarChar, Value=model.Q14},
                new SqlParameter{ParameterName="@Q14text", SqlDbType=SqlDbType.NVarChar, Value=model.Q14text},
                new SqlParameter{ParameterName="@Q15", SqlDbType=SqlDbType.NVarChar, Value=model.Q15},
                new SqlParameter{ParameterName="@Q15text", SqlDbType=SqlDbType.NVarChar, Value=model.Q15text},
                new SqlParameter{ParameterName="@Q16", SqlDbType=SqlDbType.NVarChar, Value=model.Q16},
                new SqlParameter{ParameterName="@Q16text", SqlDbType=SqlDbType.NVarChar, Value=model.Q16text},
                new SqlParameter{ParameterName="@Q17", SqlDbType=SqlDbType.NVarChar, Value=model.Q17},
                new SqlParameter{ParameterName="@Q17text", SqlDbType=SqlDbType.NVarChar, Value=model.Q17text},
                new SqlParameter{ParameterName="@Q18", SqlDbType=SqlDbType.NVarChar, Value=model.Q18},
                new SqlParameter{ParameterName="@Q18text", SqlDbType=SqlDbType.NVarChar, Value=model.Q18text},
                new SqlParameter{ParameterName="@Q19", SqlDbType=SqlDbType.NVarChar, Value=model.Q19},
                new SqlParameter{ParameterName="@Q19text", SqlDbType=SqlDbType.NVarChar, Value=model.Q19text},
                new SqlParameter{ParameterName="@Q20", SqlDbType=SqlDbType.NVarChar, Value=model.Q20},
                new SqlParameter{ParameterName="@Q20text", SqlDbType=SqlDbType.NVarChar, Value=model.Q20text},
                new SqlParameter{ParameterName="@Q21", SqlDbType=SqlDbType.NVarChar, Value=model.Q21},
                new SqlParameter{ParameterName="@Q21text", SqlDbType=SqlDbType.NVarChar, Value=model.Q21text},
                new SqlParameter{ParameterName="@Q22", SqlDbType=SqlDbType.NVarChar, Value=model.Q22},
                new SqlParameter{ParameterName="@Q22text", SqlDbType=SqlDbType.NVarChar, Value=model.Q22text},
                new SqlParameter{ParameterName="@Q23", SqlDbType=SqlDbType.NVarChar, Value=model.Q23},
                new SqlParameter{ParameterName="@Q23text", SqlDbType=SqlDbType.NVarChar, Value=model.Q23text},
                new SqlParameter{ParameterName="@Q24", SqlDbType=SqlDbType.NVarChar, Value=model.Q24},
                new SqlParameter{ParameterName="@Q24text", SqlDbType=SqlDbType.NVarChar, Value=model.Q24text},
                new SqlParameter{ParameterName="@Q25", SqlDbType=SqlDbType.NVarChar, Value=model.Q25},
                new SqlParameter{ParameterName="@Q25text", SqlDbType=SqlDbType.NVarChar, Value=model.Q25text},
                new SqlParameter{ParameterName="@Q26", SqlDbType=SqlDbType.NVarChar, Value=model.Q26},
                new SqlParameter{ParameterName="@Q26text", SqlDbType=SqlDbType.NVarChar, Value=model.Q26text},
                new SqlParameter{ParameterName="@email", SqlDbType=SqlDbType.NVarChar, Value=model.Email},
                new SqlParameter{ParameterName="@name", SqlDbType=SqlDbType.NVarChar, Value=model.Name},
                new SqlParameter{ParameterName="@mobile", SqlDbType=SqlDbType.NVarChar, Value=model.Mobile}
            };

            for (int i = 0; i < parms.Length; i++)
            {
                if (parms[i].Value == null)
                    parms[i].Value = DBNull.Value;
            }

            strSql1.Append("Q1,");
            strSql1.Append("Q1text,");
            strSql1.Append("Q2,");
            strSql1.Append("Q3,");
            strSql1.Append("Q4,");
            strSql1.Append("Q5,");
            strSql1.Append("Q6,");
            strSql1.Append("Q7,");
            strSql1.Append("Q7text,");
            strSql1.Append("Q8,");
            strSql1.Append("Q9,");
            strSql1.Append("Q9text,");
            strSql1.Append("Q10_1,");
            strSql1.Append("Q10_2,");
            strSql1.Append("Q10_3,");
            strSql1.Append("Q10text,");
            strSql1.Append("Q11,");
            strSql1.Append("Q11text,");
            strSql1.Append("Q12_1,");
            strSql1.Append("Q12_2,");
            strSql1.Append("Q12_3,");
            strSql1.Append("Q12text,");
            strSql1.Append("Q13,");
            strSql1.Append("Q13text,");
            strSql1.Append("Q14,");
            strSql1.Append("Q14text,");
            strSql1.Append("Q15,");
            strSql1.Append("Q15text,");
            strSql1.Append("Q16,");
            strSql1.Append("Q16text,");
            strSql1.Append("Q17,");
            strSql1.Append("Q17text,");
            strSql1.Append("Q18,");
            strSql1.Append("Q18text,");
            strSql1.Append("Q19,");
            strSql1.Append("Q19text,");
            strSql1.Append("Q20,");
            strSql1.Append("Q20text,");
            strSql1.Append("Q21,");
            strSql1.Append("Q21text,");
            strSql1.Append("Q22,");
            strSql1.Append("Q22text,");
            strSql1.Append("Q23,");
            strSql1.Append("Q23text,");
            strSql1.Append("Q24,");
            strSql1.Append("Q24text,");
            strSql1.Append("Q25,");
            strSql1.Append("Q25text,");
            strSql1.Append("Q26,");
            strSql1.Append("Q26text,");
            strSql1.Append("email,");
            strSql1.Append("name,");
            strSql1.Append("mobile");


            strSql2.Append("@Q1,");
            strSql2.Append("@Q1text,");
            strSql2.Append("@Q2,");
            strSql2.Append("@Q3,");
            strSql2.Append("@Q4,");
            strSql2.Append("@Q5,");
            strSql2.Append("@Q6,");
            strSql2.Append("@Q7,");
            strSql2.Append("@Q7text,");
            strSql2.Append("@Q8,");
            strSql2.Append("@Q9,");
            strSql2.Append("@Q9text,");
            strSql2.Append("@Q10_1,");
            strSql2.Append("@Q10_2,");
            strSql2.Append("@Q10_3,");
            strSql2.Append("@Q10text,");
            strSql2.Append("@Q11,");
            strSql2.Append("@Q11text,");
            strSql2.Append("@Q12_1,");
            strSql2.Append("@Q12_2,");
            strSql2.Append("@Q12_3,");
            strSql2.Append("@Q12text,");
            strSql2.Append("@Q13,");
            strSql2.Append("@Q13text,");
            strSql2.Append("@Q14,");
            strSql2.Append("@Q14text,");
            strSql2.Append("@Q15,");
            strSql2.Append("@Q15text,");
            strSql2.Append("@Q16,");
            strSql2.Append("@Q16text,");
            strSql2.Append("@Q17,");
            strSql2.Append("@Q17text,");
            strSql2.Append("@Q18,");
            strSql2.Append("@Q18text,");
            strSql2.Append("@Q19,");
            strSql2.Append("@Q19text,");
            strSql2.Append("@Q20,");
            strSql2.Append("@Q20text,");
            strSql2.Append("@Q21,");
            strSql2.Append("@Q21text,");
            strSql2.Append("@Q22,");
            strSql2.Append("@Q22text,");
            strSql2.Append("@Q23,");
            strSql2.Append("@Q23text,");
            strSql2.Append("@Q24,");
            strSql2.Append("@Q24text,");
            strSql2.Append("@Q25,");
            strSql2.Append("@Q25text,");
            strSql2.Append("@Q26,");
            strSql2.Append("@Q26text,");
            strSql2.Append("@email,");
            strSql2.Append("@name,");
            strSql2.Append("@mobile");

            strSql.Append("insert into QuestionsSUV(");
            strSql.Append(strSql1.ToString());
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString());
            strSql.Append(")");
            strSql.Append(";select @@IDENTITY");
            return SQLHelper.ExecuteSql(strSql.ToString(), parms);
        }

        private object RetrieveSaveValue(object obj)
        {
            if (obj != null) return obj;
            return DBNull.Value;
        }

        /// <summary>
        /// 验证邮件是否存在
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public int IsExist(string email)
        {
            string strSQL = "select count(email) from QuestionsSUV where email=@email";

            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@email",SqlDbType.NVarChar)
            };
            parms[0].Value = email;
            object obj = SQLHelper.ExecuteScalar(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, strSQL, parms);
            return Convert.ToInt32(obj.ToString());
        }
    }

}
